﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.Common.Wallet;
using Dapper;

namespace BCData.Common.Wallet
{
    public class WalletData : IWalletData
    {
        private const string QUERY = @"SELECT WalletId, UserId, Balance, PayPassword,AliPayAuthInfo,WxPayAuthInfo,PasswordErrorCount,FreezeTime 
                                        FROM Wallet";
        private const string SQL_INSERT_ITEM = @"INSERT INTO Wallet (UserId, Balance)
                                                 VALUES (?UserId, ?Balance);
                                                 {0} WHERE WalletId = LAST_INSERT_ID()";
        private const string SQL_UPDATE_ITEM_BY_WALLETID = @"UPDATE Wallet
                                                             SET UserId = ?UserId, Balance = ?Balance
                                                             WHERE WalletId = ?WalletId;
                                                             {0}
                                                             WHERE WalletId = ?WalletId";
        private const string SQL_SELECT_ITEMS = @"{0} ORDER BY WalletId DESC";
        private const string SQL_SELECT_ITEM_BY_WALLETID = @"{0} WHERE WalletId = ?WalletId";
        private const string SQL_SELECT_ITEM_BY_USERID = @"{0} WHERE UserId = ?UserId";
        private const string SQL_UPDATE_PAYPWD_BY_USERID = @"UPDATE Wallet
                                                             SET PayPassword = ?PayPassword
                                                             WHERE UserId = ?UserId;
                                                             {0} WHERE UserId = ?UserId";
        private const string SQL_UPDATE_BALANCE_BY_USERID = @"UPDATE Wallet
                                                             SET Balance = ?Balance
                                                             WHERE UserId = ?UserId;
                                                             {0} WHERE UserId = ?UserId";

        protected readonly IDatabaseContext databaseContext;

        public WalletData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public WalletData()
        {
        }

        public WalletEntity Add(WalletEntity entity)
        {
            string sql = string.Format(SQL_INSERT_ITEM, QUERY);
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?UserId", entity.UserId, DbType.Int64);
            parameters.Add("?Balance", entity.Balance, DbType.Decimal);

            return this.databaseContext.Master.QueryEntity<WalletEntity>(sql, parameters, databaseContext.Transaction);
        }

        public WalletEntity Update(WalletEntity entity)
        {
            string sql = string.Format(SQL_UPDATE_ITEM_BY_WALLETID, QUERY);
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?UserId", entity.UserId, DbType.Int64);
            parameters.Add("?Balance", entity.Balance, DbType.Decimal);
            parameters.Add("?WalletId", entity.WalletId, DbType.Int64);

            return this.databaseContext.Master.QueryEntity<WalletEntity>(sql, parameters, databaseContext.Transaction);
        }

        public IEnumerable<WalletEntity> GetPageableList(int pageIndex, int pageSize, out int dataCount, long? userId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (userId.HasValue)
            {
                wheres.Add("UserId = ?UserId");
                parameters.Add("?UserId", userId.Value, DbType.Int64);
            }
            
            string sql = string.Format("{0} {1} LIMIT {2}, {3}; SELECT COUNT(1) FROM Wallet {1}",
                string.Format(SQL_SELECT_ITEM_BY_WALLETID, QUERY),
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize);

            var results = this.databaseContext.Slave.QueryMultiple<WalletEntity, int>(sql, parameters);

            dataCount = results.Item2;

            return results.Item1;
        }

        public WalletEntity Get(long walletId)
        {
            string sql = string.Format(SQL_SELECT_ITEM_BY_WALLETID, QUERY);
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?WalletId", walletId, DbType.Int64);

            return this.databaseContext.Slave.QueryEntity<WalletEntity>(sql, parameters);
        }

        public WalletEntity GetWalletByUserId(long userId)
        {
            string sql = string.Format(SQL_SELECT_ITEM_BY_USERID, QUERY);
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?UserId", userId, DbType.Int64);

            return this.databaseContext.Slave.QueryEntity<WalletEntity>(sql, parameters);
        }
        public WalletEntity ResetPayPassword(long userId, string payPwd)
        {
            string sql = string.Format(SQL_UPDATE_PAYPWD_BY_USERID, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PayPassword", payPwd, DbType.AnsiString);
            parameters.Add("?UserId", userId, DbType.Int64);
            return this.databaseContext.Master.QueryEntity<WalletEntity>(sql, parameters, databaseContext.Transaction);
        }
        public int ChangeAliPayAuthInfo(long userId, string aliPayAuthInfo)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = @"UPDATE Wallet SET AliPayAuthInfo =?AliPayAuthInfo WHERE UserId =?UserId;";
            parameters.Add("?AliPayAuthInfo", aliPayAuthInfo, DbType.AnsiString);
            parameters.Add("?UserId", userId, DbType.Int64);
            return this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }
        public int ChangeWxPayAuthInfo(long userId, string wxPayAuthInfo)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = @"UPDATE Wallet SET WxPayAuthInfo =?WxPayAuthInfo WHERE UserId =?UserId;";
            parameters.Add("?WxPayAuthInfo", wxPayAuthInfo, DbType.AnsiString);
            parameters.Add("?UserId", userId, DbType.Int64);
            return this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public WalletEntity BalanceRecharge(long userId, decimal rechargeAmount)
        {
            string sql = string.Format(SQL_UPDATE_BALANCE_BY_USERID, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Balance", rechargeAmount, DbType.Decimal, size: 10, precision: 2);
            parameters.Add("?UserId", userId, DbType.Int64);
            return this.databaseContext.Master.QueryEntity<WalletEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<WalletEntity> UpdatePasswordErrorCount(long userId,int passwordErrorCount)
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = string.Format(@"UPDATE Wallet SET PasswordErrorCount =?PasswordErrorCount WHERE UserId =?UserId; {0} WHERE UserId =?UserId",QUERY);
            if (passwordErrorCount == 3)
            {
                sql = string.Format(@"UPDATE Wallet SET PasswordErrorCount =?PasswordErrorCount, FreezeTime=DATE_ADD(now(),INTERVAL 30 MINUTE) WHERE UserId =?UserId; {0} WHERE UserId =?UserId", QUERY);
            }
            parameters.Add("?PasswordErrorCount", passwordErrorCount, DbType.Int32);
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Master.QueryEntityAsync<WalletEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async void ClearErrorCount()
        {
            DynamicParameters parameters = new DynamicParameters();
            string sql = @"UPDATE Wallet SET PasswordErrorCount=?PasswordErrorCount WHERE FreezeTime<now() AND PasswordErrorCount=3;";
            await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
        }
        
    }
}
